from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import sessionmaker

from commons.entity.po.reimbursement import Reimbursement
from commons.mappers.base_mapper import BaseMapper

# 创建数据库引擎
db_url = "mysql+pymysql://root:123456@localhost/ctdb"
engine = create_engine(db_url, echo=True)

# 创建会话类
Session = sessionmaker(bind=engine)


class ReimbursementMapper(BaseMapper):
    def __init__(self):
        super().__init__(Session)

    def select_by_reimbursement_id(self, reimbursement_id):
        """
        根据 reimbursement_id 从数据库获取 Reimbursement 对象
        :param reimbursement_id: str
        :return: Reimbursement 对象或 None
        """
        try:
            with self.session_factory() as session:
                reimbursement = session.query(Reimbursement).filter_by(reimbursement_id=reimbursement_id).first()
                if reimbursement:
                    return reimbursement
                return None
        except SQLAlchemyError as e:
            print(f"Database error: {str(e)}")
            return None

    def delete_by_reimbursement_id(self, reimbursement_id):
        """
        根据 reimbursement_id 删除报销记录
        :param reimbursement_id: str
        :return: True（删除成功）或 False（失败）
        """
        try:
            with self.session_factory() as session:
                reimbursement = session.query(Reimbursement).filter_by(reimbursement_id=reimbursement_id).first()
                if reimbursement:
                    session.delete(reimbursement)
                    session.commit()  # 提交事务
                    return True
                return False  # 报销记录不存在
        except SQLAlchemyError as e:
            print(f"Database error during delete operation: {str(e)}")
            return False

    def update_by_reimbursement_id(self, reimbursement_update, reimbursement_id):
        """
        根据 reimbursement_id 更新报销信息
        :param reimbursement_id: str
        :param reimbursement_update: Reimbursement 类实例或字典，包含需要更新的报销属性
        :return: True（更新成功）或 False（失败）
        """
        try:
            with self.session_factory() as session:
                reimbursement = session.query(Reimbursement).filter_by(reimbursement_id=reimbursement_id).first()
                if reimbursement:
                    # 如果 reimbursement_update 是类实例，转换为字典
                    if reimbursement_update and hasattr(reimbursement_update, 'to_dict'):
                        reimbursement_update = reimbursement_update.to_dict()

                    # 使用新数据更新报销记录属性
                    for key, value in reimbursement_update.items():
                        if value is not None and hasattr(reimbursement, key):
                            setattr(reimbursement, key, value)

                    session.commit()  # 提交事务
                    return True
                return False  # 报销记录不存在
        except SQLAlchemyError as e:
            print(f"Database error during update operation: {str(e)}")
            return False

    def insert_reimbursement(self, reimbursement_data):
        """
        将报销数据插入数据库。
        :param reimbursement_data: 包含报销信息的字典。
        """
        try:
            with self.session_factory() as session:
                # 假设你有一个Reimbursement模型
                reimbursement = Reimbursement(
                    food_cost=reimbursement_data["food_cost"],
                    transport_cost=reimbursement_data["transport_cost"],
                    hotel_cost=reimbursement_data["hotel_cost"],
                    apply_date=reimbursement_data["apply_date"],
                    detail=reimbursement_data["detail"],
                    travel_id=reimbursement_data["travel_id"]
                )
                session.add(reimbursement)
                session.commit()
                return True
        except SQLAlchemyError as e:
            print(f"Database error: {str(e)}")
            return False

    def select_by_travel_id(self, travel_id):
            """
            :param travel_id: int
            :return: Travel 对象或 None
            """
            try:
                with self.session_factory() as session:
                    reimbursement = session.query(Reimbursement).filter_by(travel_id=travel_id).first()
                    if reimbursement:
                        return reimbursement
                    return None
            except SQLAlchemyError as e:
                print(f"Database error: {str(e)}")
                return None